﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data;

namespace Web.Dal
{
    public class MyApplicationConfig : INotifyPropertyChanged
    {
        #region Constant
        private static MyApplicationConfig applicationConfig;
        private static string dbName = System.Configuration.ConfigurationManager.AppSettings["dbName"]; // "TMDRFONLINE";

        private string getConnectonsString(string sqlServer, string sqlUserID, string sqlPassword)
        {
            return string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}",
                sqlServer, dbName, sqlUserID, sqlPassword);
        }

        #endregion

        #region fieldds
        private string wsservername = System.Configuration.ConfigurationManager.AppSettings["wsservername"]; // ;
        private string sqlservername = System.Configuration.ConfigurationManager.AppSettings["sqlservername"]; // ;
        private string sqluserid = System.Configuration.ConfigurationManager.AppSettings["sqluserid"]; // ;
        private string sqlpassword = System.Configuration.ConfigurationManager.AppSettings["sqlpassword"]; // ;
        private string rptpath = System.Configuration.ConfigurationManager.AppSettings["rptpath"]; // ;

        #endregion

        #region Property
        public string WsServerName
        {
            get { return wsservername; }
            set { wsservername = value; }
        }

        public string SqlServerName
        {
            get { return sqlservername; }
            set
            {
                if (sqlservername != value)
                {
                    sqlservername = value;
                    OnPropertyChanged("SqlUserID");
                    OnPropertyChanged("SqlPassword");
                }
            }
        }

        public string SqlUserID
        {
            get { return sqluserid; }
            set
            {
                if (sqluserid != value)
                {
                    sqluserid = value;
                    OnPropertyChanged("SqlServerName");
                    OnPropertyChanged("SqlPassword");
                }
            }
        }

        public string SqlPassword
        {
            get { return sqlpassword; }
            set
            {
                if (sqlpassword != value)
                {
                    sqlpassword = value;
                    OnPropertyChanged("SqlServerName");
                    OnPropertyChanged("SqlUserID");
                }
            }
        }

        public string RptPath
        {
            get { return rptpath; }
            set { rptpath = value; }
        }

        public string SqlConnectionStringDb
        {
            get
            {
                return getConnectonsString(sqlservername, sqluserid, sqlpassword);
            }

        }
        #endregion

        #region Constructor
        public static MyApplicationConfig getApplicationConfig()
        {
            if (applicationConfig == null)
            {
                applicationConfig = new MyApplicationConfig();
            }
            return applicationConfig;
        }

        #endregion

        #region INotifyPropertyChanged Members
        public event PropertyChangedEventHandler PropertyChanged;
        protected virtual void OnPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
        #endregion

    }

    public class DbsException : Exception
    {
        public DbsException() { }

        public DbsException(string message)
            : base(message)
        {
        }

        public DbsException(string message, Exception inner)
            : base(message, inner)
        {
        }
    }

    public class ConnectionsSql : IDisposable
    {
        #region Member
        private string cnString = "";

        #endregion

        #region Fields and Property
        private SqlConnection connection;
        public SqlConnection Connection
        {
            get { return connection; }
            set { connection = value; }
        }
        private SqlTransaction transaction;
        public SqlTransaction Transaction
        {
            get { return transaction; }
            set { transaction = value; }
        }

        #endregion

        #region Constructor
        public ConnectionsSql()
        {
            try
            {
                MyApplicationConfig config;
                config = MyApplicationConfig.getApplicationConfig();
                cnString = config.SqlConnectionStringDb;

                connection = new SqlConnection(cnString);
                connection.Open();
            }
            catch (Exception err) { throw err; }
        }

        #endregion

        #region Methods
        public SqlCommand CreateSqlCommand(string sqlQuery, CommandType cmdType)
        {
            SqlCommand cmd = new SqlCommand(sqlQuery, connection);
            try
            {
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = 7200;
                cmd.Transaction = transaction;
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 11)
                    throw new DbsException("Network Error");
                else
                    throw sqlEx;
            }

            return cmd;
        }

        public DataTable CreateDataTable(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 11)
                    throw new DbsException("Network Error");
                else
                    throw sqlEx;
            }
            return dt;
        }

        public SqlDataReader CreateDataReder(SqlCommand cmd)
        {
            SqlDataReader dr;
            return dr = cmd.ExecuteReader();
        }

        public void Close()
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
                transaction = null;
            }
        }

        public void BeginTransaction()
        {
            transaction = null;
            transaction = connection.BeginTransaction();
        }

        public void Commit()
        {
            transaction.Commit();
            transaction = null;
        }

        public void Rollback()
        {
            transaction.Rollback();
            transaction = null;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            //
        }

        #endregion
    }
}